
package DBLayer;
import java.sql.*;
import ModelLayer.Company;

/**
 * @version 12/22/2011
 * @author Gruppe 4
 */

public class DBCompany implements IFDBCompany
{
    private Connection con;
    private PreparedStatement pStmtSelect;

    /**
     * Constructor for class DBCompany
     */
    public DBCompany()
    {
       con = DBConnection.getInstance().getDBcon();
    }
    
    /**
     * Method to find a company from the database
     * @param companyId int companyId the id of the company
     * @return Returns a company object
     */
    @Override
    public Company searchCompanyById(int companyId)
    {
        String wClause = "  where companyId = ";
        Company company = new Company();
        company = findWithPreQuery(companyId, wClause);
        
        return company;
    }
    
    /**
     * Method to insert a Company object into the database
     * @param companyObj an Object of the type Company
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int insertCompany(Company companyObj)
    {
        //call to get the next standardTask id
        int nextCompanyId = getMaxId();
        nextCompanyId = nextCompanyId + 1;
        System.out.println("next nextCompanyId = " +  nextCompanyId);
        PreparedStatement pstmt = null;
        int rc = -1; 
        
        String insertCompany = "insert into Company (companyId, name, fName, lName, address, zipCode, mobileNo, phoneNo, email, cvr)"+
                     "values (?,?,?,?,?,?,?,?,?,?)";
        System.out.println(insertCompany);
        try
        {
            pstmt = con.prepareStatement(insertCompany);
            pstmt.setInt(1,nextCompanyId);
            pstmt.setString(2,companyObj.getName());
            pstmt.setString(3, companyObj.getFirstName());
            pstmt.setString(4,companyObj.getLastName());
            pstmt.setString(5, companyObj.getAddress());
            pstmt.setInt(6, companyObj.getZipCode());
            pstmt.setString(7, companyObj.getMobileNo());
            pstmt.setString(8, companyObj.getPhoneNo());
            pstmt.setString(9, companyObj.getEmail());
            pstmt.setString(10, companyObj.getCvr());
            
            rc = pstmt.executeUpdate();
        }//end try
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
        
        catch(Exception e)
        {
            System.out.println("Insert exception in Company db: "+e.getMessage());
        }
        return rc; 
    }
    
    /**
     * Method to update a Company in the database;
     * @param companyObj an Object of the type Company
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int updateCompany(Company company)
    {
        Company comObj  = company;
        int rc=-1;
        PreparedStatement pstmt = null;
        
        String query="UPDATE Company SET "+
                "name = ?, "+
                "fName = ?, "+
                "lName = ?, "+
		"address = ?, "+
                "zipCode = ?, " +
                "mobileNo = ?, " +
                "phoneNo = ?, " +
                "Email = ?, " +
                "Cvr = ? " +
		" WHERE companyId = ?";
        System.out.println("Update query:" + query);
        try
        {
            //Update Company
            pstmt = con.prepareStatement(query);
                    
            pstmt.setString(1, comObj.getName());
            pstmt.setString(2, comObj.getFirstName());
            pstmt.setString(3, comObj.getLastName());
            pstmt.setString(4, comObj.getAddress());
            pstmt.setInt(5, comObj.getZipCode());
            pstmt.setString(6, comObj.getMobileNo());
            pstmt.setString(7, comObj.getPhoneNo());
            pstmt.setString(8, comObj.getEmail());
            pstmt.setString(9, comObj.getCvr());
            pstmt.setInt(10, comObj.getCompanyId());
                   
            rc = pstmt.executeUpdate();
            pstmt.close();
        }//end try
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
        
        catch(Exception e)
        {
            System.out.println("Update exception in StandardTask db: "+e.getMessage());
        }
        
        return rc;
    }
    
    /**
     * Method to delete a Company from the database
     * @param companyId int companyId the id of the company
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int deleteCompany(int companyId)
    {
        int rc=-1;
        String query="DELETE FROM Company WHERE companyId = '" +
                companyId + "'";
        System.out.println(query);
        try
        {
            // delete from company
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }//end try
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
        
        catch(Exception e)
        {
            System.out.println("Delete exception in StandardTask db: "+e.getMessage());
        }
        return rc;
    }

    /**
     * Method to build an object of type Company
     * @param results 
     * @return Returns a company object
     */
    private Company buildCompany(ResultSet results)
    {
       Company companyObj = new Company();
       try
       {
           companyObj.setCompanyId(results.getInt("companyId"));
           companyObj.setName(results.getString("name"));
           companyObj.setFirstName(results.getString("fName"));
           companyObj.setLastName(results.getString("lName"));
           companyObj.setAddress(results.getString("Address"));
           companyObj.setZipCode(results.getInt("ZipCode"));
           companyObj.setPhoneNo(results.getString("MobileNo"));
           companyObj.setMobileNo(results.getString("PhoneNo"));
           companyObj.setEmail(results.getString("Email"));
           companyObj.setCvr(results.getString("CVR"));        
       }//end try

       catch(Exception e)
       {
           System.out.println("BuildCompany exception in Company db: "+e);
       }

       return companyObj;
    }
    
    /**
     * Method to create a prepared statement 
     * @param wClause String wClause
     * @return returns a PreparedStatment
     */
    private PreparedStatement getPreparedStmt(String wClause)
    {
        try
        {
            String query = "Select * FROM Company " + wClause + " ?";
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
        }//end try
        catch(Exception e)
        {
            System.out.println("getPreparedStmt exception in Company db: "+e);
        }
        return pStmtSelect;
    }
    
    /**
     * Method that finds a Company in the database 
     * @param companyId
     * @param wClause
     * @return 
     */
    private Company findWithPreQuery(int companyId, String wClause)
    {
        System.out.println("findWithPreQuery");
        
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmt(wClause);
        }
        Company company = null;
        ResultSet results;

        try
        {
            pStmtSelect.setInt(1,companyId);
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                company = buildCompany(results);
            }
        }//end try
        catch(Exception e)
        {
            System.out.println("findWithPreQuery exception in Company db: "+e);
        }
        return company;
    } 
    
    
    /**
     * Method to get the highest companyId from the database
     * @return returns an int which is the highest companyId from the database
     */
    public int getMaxId()
    {
        int maxCompanyId = GetMax.getMaxId("Select max(companyId) from Company");
        return maxCompanyId;
    }
}